package com.zzxz.practicaltrainingsys.mapper;

import com.github.pagehelper.Page;
import com.zzxz.practicaltrainingsys.entity.Dailynews;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface DailynewsMapper {

    @Select("SELECT \n" +
            "  p.project_name,\n" +
            "  s.student_id,\n" +
            "  s.student_name,\n" +
            "  COALESCE(d.dailynews_content, '') AS dailynews_content, \n" +
            "  COALESCE(d.dailynews_time, NULL) AS dailynews_time, \n" +
            "  -- 优先取 team.group_id，无则通过 member 关联 team 获取\n" +
            "  COALESCE(t.group_id, t2.group_id) AS group_number, \n" +
            "  CASE \n" +
            "    WHEN s.student_id = t.leader_id THEN '组长'\n" +
            "    WHEN s.student_id = t.viceleader_id THEN '副组长'\n" +
            "    WHEN EXISTS (\n" +
            "      SELECT 1 \n" +
            "      FROM member m \n" +
            "      JOIN team t2 ON m.team_id = t2.team_id \n" +
            "      WHERE m.student_id = s.student_id \n" +
            "        AND t2.project_id = p.project_id \n" +
            "    ) THEN '成员'\n" +
            "    ELSE '无'\n" +
            "  END AS position_type\n" +
            "FROM \n" +
            "  student s\n" +
            "  JOIN project p ON EXISTS (\n" +
            "    SELECT 1 FROM team t \n" +
            "    WHERE t.project_id = p.project_id \n" +
            "      AND s.student_id IN (t.leader_id, t.viceleader_id)\n" +
            "    UNION ALL\n" +
            "    SELECT 1 FROM member m \n" +
            "    JOIN team t2 ON m.team_id = t2.team_id \n" +
            "    WHERE t2.project_id = p.project_id \n" +
            "      AND m.student_id = s.student_id\n" +
            "  )\n" +
            "  LEFT JOIN dailynews d \n" +
            "    ON s.student_id = d.student_id \n" +
            "    AND p.project_id = d.project_id \n" +
            "  -- 关联组长/副组长的 team 表\n" +
            "  LEFT JOIN team t \n" +
            "    ON p.project_id = t.project_id \n" +
            "    AND s.student_id IN (t.leader_id, t.viceleader_id)\n" +
            "  -- 关联成员的 team 表（通过 member 表）\n" +
            "  LEFT JOIN team t2 \n" +
            "    ON EXISTS (\n" +
            "      SELECT 1 FROM member m \n" +
            "      WHERE m.student_id = s.student_id \n" +
            "        AND m.team_id = t2.team_id \n" +
            "        AND t2.project_id = p.project_id \n" +
            "    )\n" +
            "ORDER BY \n" +
            "  p.project_name, \n" +
            "  s.student_name, \n" +
            "  d.dailynews_time DESC")
    List<Map<String, Object>> getAllDailyNews();

    //获取某个项目的日报
    @Select("SELECT \n" +
            "  p.project_name,\n" +
            "  s.student_id,\n" +
            "  s.student_name,\n" +
            "  COALESCE(d.dailynews_content, '') AS dailynews_content, \n" +
            "  COALESCE(d.dailynews_time, NULL) AS dailynews_time, \n" +
            "  -- 优先取 team.group_id，无则通过 member 关联 team 获取\n" +
            "  COALESCE(t.group_id, t2.group_id) AS group_number, \n" +
            "  CASE \n" +
            "    WHEN s.student_id = t.leader_id THEN '组长'\n" +
            "    WHEN s.student_id = t.viceleader_id THEN '副组长'\n" +
            "    WHEN EXISTS (\n" +
            "      SELECT 1 \n" +
            "      FROM member m \n" +
            "      JOIN team t2 ON m.team_id = t2.team_id \n" +
            "      WHERE m.student_id = s.student_id \n" +
            "        AND t2.project_id = p.project_id \n" +
            "    ) THEN '成员'\n" +
            "    ELSE '无'\n" +
            "  END AS position_type\n" +
            "FROM \n" +
            "  student s\n" +
            "  JOIN project p ON p.project_id = #{projectId} AND EXISTS (\n" +
            "    SELECT 1 FROM team t \n" +
            "    WHERE t.project_id = p.project_id \n" +
            "      AND s.student_id IN (t.leader_id, t.viceleader_id)\n" +
            "    UNION ALL\n" +
            "    SELECT 1 FROM member m \n" +
            "    JOIN team t2 ON m.team_id = t2.team_id \n" +
            "    WHERE t2.project_id = p.project_id \n" +
            "      AND m.student_id = s.student_id\n" +
            "  )\n" +
            "  LEFT JOIN dailynews d \n" +
            "    ON s.student_id = d.student_id \n" +
            "    AND p.project_id = d.project_id \n" +
            "  -- 关联组长/副组长的 team 表\n" +
            "  LEFT JOIN team t \n" +
            "    ON p.project_id = t.project_id \n" +
            "    AND s.student_id IN (t.leader_id, t.viceleader_id)\n" +
            "  -- 关联成员的 team 表（通过 member 表）\n" +
            "  LEFT JOIN team t2 \n" +
            "    ON EXISTS (\n" +
            "      SELECT 1 FROM member m \n" +
            "      WHERE m.student_id = s.student_id \n" +
            "        AND m.team_id = t2.team_id \n" +
            "        AND t2.project_id = p.project_id \n" +
            "    )\n" +
            "ORDER BY \n" +
            "  p.project_name, \n" +
            "  s.student_name, \n" +
            "  d.dailynews_time DESC")
    List<Map<String, Object>> getDailyNewsByProjectId(@Param("projectId") Integer projectId);

    //原来前端查询日报接口
    @Select("SELECT p.project_name, s.student_id, s.student_name, d.dailynews_content, d.dailynews_time " +
            "FROM project p " +
            "JOIN student s ON p.school_id = s.school_id AND p.college_id = s.college_id AND p.major_id = s.major_id AND p.grade_id = s.grade_id " +
            "JOIN dailynews d ON s.student_id = d.student_id AND p.project_id = d.project_id " +
            "ORDER BY d.dailynews_time DESC")
    List<Map<String, Object>> getAllDailyReports();

    //用来联合学生表的学号和项目id来查该项目该学生的日报
    @Select("SELECT d.* FROM student s " +
            "LEFT JOIN dailynews d ON s.student_id = d.student_id " +
            "WHERE s.student_id = #{studentId} AND d.project_id = #{projectId}")
    List<Dailynews> getDailynewsByStudentIdAndProjectId(
            @Param("studentId") Integer studentId,
            @Param("projectId") Integer projectId);

    //日报详情
    @Select("SELECT * FROM dailynews WHERE dailynews_id = #{dailynewsId}")
    Dailynews getDailynewsById(Integer dailynewsId);

    //新增日报
    @Insert("INSERT INTO dailynews (student_id, dailynews_content, dailynews_time, project_id) " +
            "VALUES (#{student_id}, #{dailynews_content},sysdate(), #{project_id})")
    int insertDailynews(Dailynews dailynews);

    // 更新日报信息
    @Update("UPDATE dailynews " +
            "SET dailynews_content = #{dailynews_content}, " +
            "    dailynews_time = #{dailynews_time} " +
            "WHERE dailynews_id = #{dailynews_id}")
    int updateDailynews(Dailynews dailynews);




}
